use rusqlite::params;
use rusqlite::Connection;

use std::collections::HashMap;
use serde::Serialize;

// 定义返回的数据结构
#[derive(Serialize)]
pub struct CategoryCount {
    #[serde(rename = "1")] // 示例字段名，实际不需要这样
    count: u32,
}

// 实际我们需要返回的是HashMap<String, u32>
pub type CategoryCountMap = HashMap<String, u32>;

#[derive(Debug, serde::Serialize)]
pub struct CategoryInfo {
    id: i64,
    name: String,
    sort: i64,
    gmt_create: i64,
    gmt_modified: i64,
}

#[tauri::command]
pub fn get_code_count_by_category() -> CategoryCountMap {
    let conn = Connection::open("./code_snippet.db").unwrap();

    // 使用单个查询获取所有分类的计数（更高效）
    let sql = "
        SELECT c.id, COUNT(i.id)
        FROM category_info c
        LEFT JOIN code_info i ON c.id = i.category_id
        GROUP BY c.id
    ";

    let mut stmt = conn.prepare(sql).unwrap();
    let mut rows = stmt.query([]).unwrap();

    let mut result = HashMap::new();

    while let Some(row) = rows.next().unwrap() {
        let category_id: i64 = row.get(0).unwrap();
        let count: u32 = row.get(1).unwrap();
        result.insert(category_id.to_string(), count);
    }

    result
}

#[tauri::command]
pub fn create_category_info(name: String, sort: i64, gmt_create: i64, gmt_modified: i64) {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    conn.execute(
        "insert into category_info (name, sort, gmt_create, gmt_modified) values (?1, ?2, ?3, ?4)",
        ((&name), (&sort), (&gmt_create), (&gmt_modified))
    )
    .unwrap();
}

#[tauri::command]
pub fn update_category_info(id: i64, name: String, sort: i64, gmt_modified: i64) {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();

        // 更新 category_info 表
        conn.execute(
            "UPDATE category_info SET name = ?1, sort = ?2, gmt_modified = ?3 WHERE id = ?4",
            (&name, &sort, &gmt_modified, &id)
        ).unwrap();

        // 更新关联的 code_info 表中的 category_name
        conn.execute(
            "UPDATE code_info SET category_name = ?1 WHERE category_id = ?2",
            (&name, &id)
        ).unwrap();
}

#[tauri::command]
pub fn delete_category_info(id: i64) -> bool {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    // 检查是否有数据
    let mut stmt = match conn.prepare("SELECT COUNT(*) FROM code_info WHERE category_id = ?1") {
        Ok(stmt) => stmt,
        Err(_) => return false,
    };

    let count: i64 = match stmt.query_row([id], |row| row.get(0)) {
        Ok(count) => count,
        Err(_) => return false,
    };

    if count > 0 {
        return false; // 有数据，不能删
    }

    conn.execute("DELETE FROM category_info WHERE id = ?1", [id])
        .is_ok()
}

#[tauri::command]
pub fn list_category_info(name: String) -> Vec<CategoryInfo> {
    let conn: Connection = Connection::open("./code_snippet.db").unwrap();
    let sql = "select * from category_info where name like ?1 order by sort asc";
    let mut stmt = conn.prepare(sql).unwrap();
    let name_str = format!("%{}%", name);
    let mut rows = stmt.query(params![name_str]).unwrap();
    let mut list: Vec<CategoryInfo> = Vec::new();
    while let Some(row) = rows.next().unwrap() {
        let c = CategoryInfo {
            id: row.get(0).unwrap(),
            name: row.get(1).unwrap(),
            sort: row.get(2).unwrap(),
            gmt_create: row.get(3).unwrap(),
            gmt_modified: row.get(4).unwrap(),
        };
        list.push(c);
    }
    list
}
